package system.queryer;

import java.lang.reflect.Field;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import system.mysql_connector.MySQL_Connector;
import system.parser.Sys_ModelParser;
import system.parser.Sys_SQLParser;

/**
 * <p>The basic class for simple database operations</p>
 * <ol>
 * <li>Select</li>
 * <li>Insert</li>
 * <li>Update</li>
 * <li>Delete</li>
 * </ol>
 * @author Roman Law
 * @version 1.0.0
 */
public class MySQL_Queryer {
	
	private List rsList = new ArrayList();
	private Sys_SQLParser SQL_parser = new Sys_SQLParser();
	//========================
	//Select Relative -- Begin
	//========================
	/**
	 * Find a single object by primary key.
	 * @param primaryKey key value
	 * @param key primary key name
	 * @param model model instance
	 * @return An java object
	 */
	public Object toFindByPK(String primaryKey, String key, Object model){
		String modelName = model.getClass().getSimpleName();
		List li = this.toFind("SELECT * FROM "+modelName+" WHERE "+primaryKey+" = '"+key+"'", model, null, null);
		if(li.size() == 0){
			return null;
		}else{
			return li.get(0);
		}
	}
	
	/**
	 * @param SQL - the `select` statement
	 * @param model - the model that related to the query result[<font color='purple'>Optional</font>]
	 * @param from - the begin row to be fetched
	 * @param to - the last row can be fetched
	 * @return a List of results. <br><font color='red'><b>If</b></font> a model is given, 
	 * then return a list of the entities of that model,
	 * <br> <font color='red'><b>else</b></font> return a list of Map(s)=>[java.util.Map] 
	 */
	public List toFind(String SQL, Object model, Integer from, Integer amount){
		this.excuteF(SQL, from, amount);
		if(model!=null){
			Sys_ModelParser sysMP = new Sys_ModelParser();
			return sysMP.parser(model, this.rsList);
		}else{
			return this.rsList;
		}		
	}	
	//========================
	//Select relative -- END
	//========================
	
	//========================
	//Insert relative -- Begin
	//========================
	/**
	 * Execute a pure insert SQL make by yourself
	 * @deprecated you can form an object to fulfill this action. AT version: 1.2.0
	 * @param SQL
	 * @throws SQLException 
	 */
	@Deprecated
	public void toInsert(String SQL) throws SQLException{
		this.excuteI_U_D(SQL);
	}
	
	/**
	 * @param model
	 */	
	public void toInsertSingle(Object model){		
		this.executeInsert(model);	
	}
	
	/**
	 * @param models
	 */
	public void toInsert(List models){
		this.blendBatchPreInsertStatement(models);	
	}
	//========================
	//Insert relative -- END
	//========================
	
	//========================
	//Update relative -- Begin
	//========================
	
	/**
	 * Execute a pure update SQL make by yourself
	 * @param SQL
	 * @deprecated you can form an object to fulfill this action. AT version: 1.2.0
	 * @return
	 * @throws SQLException 
	 */
	public int toUpdate(String SQL) throws SQLException{
		return this.excuteI_U_D(SQL);
	}
	
	
	/**
	 * @param model
	 * @param Index
	 * @return
	 */
	public int toUpdate(Object model,String[] Index){
		Integer effectedRows = 0;
		try{
			effectedRows = this.executeUpdate(model, null, Index);
		}catch(Exception e){
			e.printStackTrace();
		}
		return effectedRows;
	}
	
	/**
	 * @param models
	 * @param Index
	 */
	public void toUpdate(List models,String[] Index){
		this.blendBatchPreUpdateStatement(models, null, Index);
	}
	
	/**
	 * @param model
	 * @param fieldNames
	 * @param Index
	 * @return
	 */
	public int toUpdate(Object model, String[] fieldNames, String[] Index){
		Integer effectedRow = 0;		
		if((effectedRow = this.executeUpdate(model, fieldNames, Index))!=null){
			return effectedRow;
		}		
		return 0;
		
	}
	
	/**
	 * @param models
	 * @param fieldNames
	 * @param Index
	 */
	public void toUpdate(List models, String[] fieldNames, String[] Index){
		this.blendBatchPreUpdateStatement(models, fieldNames, Index);
	}
	
	//========================
	//Update relative -- End
	//========================
	
	//========================
	//Delete relative -- Begin
	//========================
	/**
	 * Execute a pure delete SQL make by yourself. 
	 * @deprecated you can form an object to fulfill this action. AT version: 1.2.0
	 * @param SQL
	 * @throws SQLException 
	 */
	@Deprecated
	public void toDelete(String SQL) throws SQLException{
		this.excuteI_U_D(SQL);
	}
	
	public void toDelete(Object model, String[] Index){
		this.executeDelete(model, Index);
	}
	//========================
	//Delete relative -- End
	//========================
	
	//========================
	//Executors
	//========================
	
	@Deprecated
	private int excuteI_U_D(String SQL) throws SQLException{
		int row_affected = 0;		
		Connection conn = MySQL_Connector.getConnection();
		Statement stat = conn.createStatement();
		row_affected = stat.executeUpdate(SQL);		
		stat.close();
		//Close Connection
		MySQL_Connector.closeConnection(conn);
		return row_affected;
	}
	
	private void excuteF(String SQL, Integer from, Integer amount){
		try {
			Connection conn = MySQL_Connector.getConnection();
			Statement stat = conn.createStatement();
			SQL += (from!=null)?" limit "+(from-1)+((amount!=null)?","+amount:""):"";
			stat.executeQuery(SQL);			
			ResultSet rs = stat.getResultSet();				
			this.rsList = this.list(rs);			
			rs.close();
			stat.close();
			//Close Connection
			MySQL_Connector.closeConnection(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
		
	private int executeInsert(Object model){
		int effectedRow = 0;		
		try {
			//Get connection
			Connection conn = MySQL_Connector.getConnection();
			//Generate prepared statement SQL
			String preSql = this.SQL_parser.genPreInsertSql(model);
			//Create prepared statement on the connection
			PreparedStatement preState = conn.prepareStatement(preSql);
			//Fill values into prepared statement
			preState = this.SQL_parser.preInsertStatementParse(model, preState);
			//Do insert
			effectedRow = preState.executeUpdate();
			//Close Connection
			MySQL_Connector.closeConnection(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return effectedRow;
	}
	
	private int executeUpdate(Object model, String[] fieldNames,String[] Index){
		int effectedRow = 0;
		try {
			//Get connection
			Connection conn = MySQL_Connector.getConnection();
			//get all field names if fields is null
			fieldNames = (fieldNames == null)?this.getAllFields(model):fieldNames;
			//Generate prepared statement SQL
			String preSql = this.SQL_parser.genPreUpdateSql(model, fieldNames, Index);
			//Create prepared statement on the connection
			PreparedStatement preState = conn.prepareStatement(preSql);
			//Fill values into prepared statement
			preState = this.SQL_parser.preUpdateStatementParse(model, preState, fieldNames, Index);			
			//Do update
			effectedRow = preState.executeUpdate();
			//Close Connection
			MySQL_Connector.closeConnection(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return effectedRow;
	}
	
	private int executeDelete(Object model, String[] Index){
		int effectedRow = 0;
		try {
			//Get connection
			Connection conn = MySQL_Connector.getConnection();
			//Generate prepared statement SQL
			String preSql = this.SQL_parser.genPreDeleteSql(model, Index);
			//Create prepared statement on the connection
			PreparedStatement preState = conn.prepareStatement(preSql);
			//Fill values into prepared statement
			preState = this.SQL_parser.preDeleteStatementParse(model, preState, Index);	
			//Do delete
			effectedRow = preState.executeUpdate();
			//Close Connection
			MySQL_Connector.closeConnection(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return effectedRow;
	}
	
	private List list(ResultSet rs){
		List result = new ArrayList();		
		try{
			ResultSetMetaData rsmd = rs.getMetaData();
			while(rs.next()){
				Map row_content = new HashMap();
				for(Integer i=1;i<=rsmd.getColumnCount();i++){
					
					row_content.put(rsmd.getColumnLabel(i), rs.getObject(rsmd.getColumnLabel(i)));
				}
				result.add(row_content);
			}
		}catch (Exception e){
			e.printStackTrace();
		}		
		return result;
	}
	
	//========================
	//Batch Order
	//========================
	
	private void blendBatchPreInsertStatement(List modelList){
		try{
			long start = System.currentTimeMillis();
			Connection con = MySQL_Connector.getConnection();
			String sql = this.SQL_parser.genPreInsertSql(modelList.get(0));
			PreparedStatement preState = con.prepareStatement(sql);
			con.setAutoCommit(false);
			for(Integer i =0; i<modelList.size();i++){
				Object obj = modelList.get(i);
				preState = this.SQL_parser.preInsertStatementParse(obj, preState);
				preState.addBatch();
			}
			preState.executeBatch();
			con.commit();
			preState.close();
			MySQL_Connector.closeConnection(con);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	private void blendBatchPreUpdateStatement(List modelList, String[] fields, String[] Index){
		try{
			long start = System.currentTimeMillis();
			Connection con = MySQL_Connector.getConnection();
			//get all field names if fields is null
			fields = (fields == null)?this.getAllFields(modelList.get(0)):fields;
			String sql = this.SQL_parser.genPreUpdateSql(modelList.get(0), fields, Index);
			PreparedStatement preState = con.prepareStatement(sql);
			con.setAutoCommit(false);
			for(Integer i =0; i<modelList.size();i++){
				Object obj = modelList.get(i);
				preState = this.SQL_parser.preUpdateStatementParse(obj, preState, fields, Index);
				preState.addBatch();
			}
			preState.executeBatch();
			con.commit();
			preState.close();
			MySQL_Connector.closeConnection(con);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	private void blendBatch(List<String> SQLs){
		try{
			long start = System.currentTimeMillis();
			Connection con = MySQL_Connector.getConnection();
			Statement stat = con.createStatement();		
			con.setAutoCommit(false);
			for(Integer i =0; i<SQLs.size();i++){
				stat.addBatch(SQLs.get(i));
			}
			stat.executeBatch();
			con.commit();
			stat.close();
			MySQL_Connector.closeConnection(con);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	private String[] getAllFields(Object Instance){
		List<String> fieldNameList = new ArrayList<String>();
		Field[] fields = Instance.getClass().getDeclaredFields();
		for(int i=0;i<fields.length;i++){
			fieldNameList.add(fields[i].getName());
		}
		String[] fieldNames = {};
		fieldNames = fieldNameList.toArray(fieldNames);
		return fieldNames;
	}

}
